<?php

namespace App\Repository;

use App\Entity\Page;
use App\Enum\PageEnum;
use App\Message\SubscribePage;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\Messenger\MessageBusInterface;
use Symfony\Component\Security\Core\Security;

/**
 * @method Page|null find($id, $lockMode = null, $lockVersion = null)
 * @method Page|null findOneBy(array $criteria, array $orderBy = null)
 * @method Page[]    findAll()
 * @method Page[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class PageRepository extends ServiceEntityRepository {
    private $security;
    private $bus;

    public function __construct (ManagerRegistry $registry, Security $security, MessageBusInterface $bus) {
        parent::__construct($registry, Page::class);
        $this->security = $security;
        $this->bus = $bus;
    }

    /**
     * 根据文章标题获取page_id集合
     * @param $value
     * @return array
     */
    public function getPageIdsByTitle ($value) {
        $result = [];

        $objs = $this->createQueryBuilder('p')
            ->select('p.id')
            ->andWhere('p.status != -1')
            ->andWhere('p.title like :title')
            ->setParameter('title', '%'.$value.'%')
            ->getQuery()
            ->getArrayResult();
        foreach ($objs as $obj) {
            array_push($result, $obj['id']);
        }

        return $result;
    }

    /**
     * 推荐博文
     * @param int $limit
     * @return mixed[]
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function getRecommendPage ($limit = 3) {
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'SELECT p.id,p.title,p.thumbnail,p.publish_time,c.value as category
                from page as p 
                left join page_category as c on p.page_category_id = c.id 
                where p.status = 1 and p.is_recommend = 1 
                order by p.id DESC 
                limit 0,'.$limit;
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * 最新博文
     * @param int $limit
     * @return mixed[]
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function getNewPage ($limit = 5) {
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'SELECT p.id,p.title,p.thumbnail,p.publish_time,p.page_like,p.page_tags,p.summary,c.value as category,a.avatar
                from page as p 
                left join page_category as c on p.page_category_id = c.id 
                left join admin_user as a on p.admin_user_id = a.id 
                where p.status = 1 and p.is_top != 1
                order by p.id DESC 
                limit 0,'.$limit;
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }


    /**
     * 热门博文
     * @param int $limit
     * @param bool $is_auth
     * @return mixed[]
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function getHotPage ($limit = 3, $is_auth = false) {
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'SELECT p.id,p.title,p.thumbnail,p.publish_time,p.page_view,c.value as category,a.avatar,a.username
                from page as p 
                left join page_category as c on p.page_category_id = c.id 
                left join admin_user as a on p.admin_user_id = a.id 
                where p.status = 1 ';
        if ($is_auth) {
            if (!$this->security->getUser()->isSuperAdmin()) {
                $sql .= ' and p.admin_user_id = '.$this->security->getUser()->getId().' ';
            }
        }
        $sql .= 'order by p.page_view DESC limit 0,'.$limit;
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * 获取文章数
     * @param bool $all
     * @return mixed
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function getPageTotal ($all = false) {
        $query = $this->createQueryBuilder('p')
            ->select('count(p.id)')
            ->andWhere('p.status != -1');
        if (!$all && !$this->security->getUser()->isSuperAdmin()) {
            $query->andWhere('p.admin_user_id = '.$this->security->getUser()->getId());
        }
        return $query->getQuery()->getSingleScalarResult();
    }

    /**
     * 获取文章阅读量
     * @param bool $all
     * @return mixed
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function getPageViewTotal ($all = false) {
        $query = $this->createQueryBuilder('p')
            ->select('sum(p.page_view) as num')
            ->andWhere('p.status != -1');
        if (!$all && !$this->security->getUser()->isSuperAdmin()) {
            $query->andWhere('p.admin_user_id = '.$this->security->getUser()->getId());
        }
        return $query->getQuery()->getSingleScalarResult();
    }

    /**
     * 获取当前用户发布的文章ID集合
     * @return array
     */
    public function getPageIds () {
        return $this->createQueryBuilder('p')
            ->select('p.id')
            ->andWhere('p.status != -1')
            ->andWhere('p.admin_user_id = '.$this->security->getUser()->getId())
            ->getQuery()
            ->getArrayResult();
    }

    /**
     * 清空置顶文章
     * @param bool $debar
     * @param int $page_id
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function preTop ($debar = false, $page_id = 0) {
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'UPDATE page SET is_top = 0 WHERE is_top = 1 ';
        if ($debar) {
            $sql .= 'AND id != '.$page_id;
        }
        $stmt = $conn->prepare($sql);
        $stmt->execute();
    }

    /**
     * 获取指定标签的文章数
     * @param $page_tag_id
     * @return int|mixed
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function getPageCountByPageTagId ($page_tag_id) {
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'SELECT count(p.id) as total
                from page as p 
                where p.status != -1 and JSON_CONTAINS(p.page_tags->\'$[*]\', \''.$page_tag_id.'\', \'$\')';
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll();
        return $result[0]['total'] ?? 0;
    }

    /**
     * 获取指定类别文章数
     * @param array $page_category_ids
     * @return mixed
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function getPageCountByPageCategoryIds ($page_category_ids = []) {
        $ids = implode(',', $page_category_ids);
        return $this->createQueryBuilder('p')
            ->select('count(p.id)')
            ->andWhere('p.status != -1')
            ->andWhere('p.page_category_id in ('.$ids.')')
            ->getQuery()
            ->getSingleScalarResult();
    }

    /**
     * 定时发布文章
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function publishPageByCron () {
        $date = date('Y-m-d H:i:s');
        // 获取待发布文章ID集合
        $items = $this->createQueryBuilder('p')
            ->select('p.id')
            ->andWhere('p.status = 0')
            ->andWhere('p.publish_type = :publish_type')
            ->andWhere('p.publish_time <= :publish_time')
            ->setParameter('publish_type', PageEnum::PUBLISH_TYPE_TIMING)
            ->setParameter('publish_time', $date)
            ->getQuery()
            ->getArrayResult();

        // 发布定时文章
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'UPDATE page SET status = 1 WHERE status = 0 AND publish_type = \''.PageEnum::PUBLISH_TYPE_TIMING.'\' AND publish_time <= \''.$date.'\'';
        $stmt = $conn->prepare($sql);
        $stmt->execute();

        // 发送订阅邮件
        foreach ($items as $item) {
            $this->bus->dispatch(new SubscribePage($item['id']));
        }
    }

    /**
     * 获取上一篇文章
     * @param $id
     * @return mixed
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function getPrevPage ($id) {
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'SELECT p.id,p.title,p.thumbnail,p.publish_time,p.page_view,c.value as category,a.avatar,a.username,a.nickname
                from page as p 
                left join page_category as c on p.page_category_id = c.id 
                left join admin_user as a on p.admin_user_id = a.id 
                where p.status = 1 and p.id < '.$id.' order by id desc limit 0,1';
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetch();
    }

    /**
     * 获取下一篇文章
     * @param $id
     * @return mixed
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function getNextPage ($id) {
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'SELECT p.id,p.title,p.thumbnail,p.publish_time,p.page_view,c.value as category,a.avatar,a.username,a.nickname
                from page as p 
                left join page_category as c on p.page_category_id = c.id 
                left join admin_user as a on p.admin_user_id = a.id 
                where p.status = 1 and p.id > '.$id.' order by id asc limit 0,1';
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetch();
    }

    /**
     * 获取对应文章相关推荐
     * @param $id
     * @param int $limit
     * @return array|mixed[]
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function getRelatedPage ($id, $limit = 2) {
        $result = [];

        $obj = $this->find($id);
        if (is_object($obj)) {
            $conn = $this->getEntityManager()->getConnection();
            $query = 'SELECT p.id,p.title,p.thumbnail,p.publish_time,p.page_view,c.value as category,a.avatar,a.username,a.nickname
                from page as p 
                left join page_category as c on p.page_category_id = c.id 
                left join admin_user as a on p.admin_user_id = a.id 
                where p.status = 1 and p.id != '.$id;
            $sql = $query.' and p.page_category_id = '.$obj->getPageCategoryId().' order by p.page_view desc limit 0,'.$limit;
            $stmt = $conn->prepare($sql);
            $stmt->execute();
            $result = $stmt->fetchAll();
            if (!$result) {
                $sql = $query.' order by p.page_view desc limit 0,'.$limit;
                $stmt = $conn->prepare($sql);
                $stmt->execute();
                $result = $stmt->fetchAll();
            }
        }

        return $result;
    }

    /**
     * 获取置顶文章
     * @return array|false|mixed
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function getTopPage () {
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'SELECT p.id,p.title,p.thumbnail,p.publish_time,p.page_like,p.page_tags,p.summary,c.value as category,a.avatar
                from page as p 
                left join page_category as c on p.page_category_id = c.id 
                left join admin_user as a on p.admin_user_id = a.id 
                where p.status = 1 and p.is_top = 1
                order by p.id DESC 
                limit 0, 1';
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAssociative();
    }
}
